﻿USE MASTER
GO
IF EXISTS 
(
	SELECT *
	FROM SYS.DATABASES
	WHERE NAME = 'QLVT'
)
DROP DATABASE QLVT

CREATE DATABASE QLVT
ON
(
	NAME = QLVT_DATA ,
	FILENAME = 'D:\QLVT_DATA.MDF',
	SIZE=50MB,
	MAXSIZE=300MB,
	FILEGROWTH=50KB
)
LOG ON
(
	NAME = QLVT_LOG ,
	FILENAME = 'D:\QLVT_LOG.LDF',
	SIZE=10MB,
	MAXSIZE=50MB,
	FILEGROWTH=10KB	
)

---------------TAO BANG-------------------

GO
USE QLVT
GO

--------------BANG NHOM KHACH HANG, NHA CHUNG CAP--------

IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='NHOM')
	DROP TABLE NHOM
GO
CREATE TABLE NHOM
(
	 MANHOM 	NVARCHAR(20) 	NOT NULL,
	 TENNHOM 	NVARCHAR(255)	NOT NULL,
	 DIENGIAI 	NVARCHAR(255),
	 LOAI NVARCHAR(15) ,
	 CONSTRAINT PK_NHOM 	PRIMARY KEY(MANHOM)
)
------------ BANG KHACH HANG -------------

IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='KHACHHANG')
	DROP TABLE KHACHHANG
GO
CREATE TABLE KHACHHANG
(
	 MAKH 	NVARCHAR(20) 	NOT NULL,
	 TENKH 	NVARCHAR(255)	NOT NULL,
	 MANHOM NVARCHAR(20) ,
	 DIACHI	NVARCHAR(255)	,
	 MASOTHUE NVARCHAR(10)	,
	 DIENTHOAI NVARCHAR(15),
	 CONSTRAINT PK_KHACHHANG 	PRIMARY KEY(MAKH)
)

-----------------------BANG NHA CUNG CAP ----------------
GO
USE QLVT
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='NHACUNGCAP')
	DROP TABLE NHACUNGCAP
GO
CREATE TABLE NHACUNGCAP
(
	 MANCC 	NVARCHAR(20) 	NOT NULL,
	 TENNCC 	NVARCHAR(255)	NOT NULL,
	 DIACHI	NVARCHAR(255),
	 MANHOM NVARCHAR(20),
	 MASOTHUE NVARCHAR(10),
	 DIENTHOAI NVARCHAR(15),
	 FAX NVARCHAR(15),
	 EMAIL NVARCHAR(50),
	 WEBSITE NVARCHAR(50),
	 CONSTRAINT PK_NHACUNGCAP 	PRIMARY KEY(MANCC)
)

------------ BANG KHO-------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='KHO')
	DROP TABLE KHO
GO
CREATE TABLE KHO
(
	 MAKHO 	NVARCHAR(20) 	NOT NULL,
	 TENKHO NVARCHAR(50)	NOT NULL,
	 DIACHI NVARCHAR(255),
	 CONSTRAINT PK_KHO 	PRIMARY KEY(MAKHO)
) 

---------------BANG DON VI CONG TAC CUA NHAN VIEN----------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='DONVI')
	DROP TABLE DONVI
GO
CREATE TABLE DONVI
(
	 MADONVI 	NVARCHAR(20) 	NOT NULL,
	 TENDONVI NVARCHAR(255)	NOT NULL,
	 DIACHI NVARCHAR(255),
	 CAPTOCHUC NVARCHAR(255),
	 CONSTRAINT PK_DONVI 	PRIMARY KEY(MADONVI)
)

----------------BANG NHAN VIEN-----------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='NHANVIEN')
	DROP TABLE NHANVIEN
GO
CREATE TABLE NHANVIEN
(
	 MANHANVIEN 	NVARCHAR(20) 	NOT NULL,
	 TENNHANVIEN NVARCHAR(255)	NOT NULL,
	 GIOITINH	NVARCHAR(3) ,
	 NGAYSINH	SMALLDATETIME,
	 MASOTHUE	NVARCHAR(10),
	 CMND	NVARCHAR(10),
	 NGAYCAP	 SMALLDATETIME,
	 NOICAP	NVARCHAR(50),
	 CHUCDANH	NVARCHAR(50),
	 DONVI	NVARCHAR(20),
	 LUONGTHOATHUAN NVARCHAR(50),
	 HESOLUONG	FLOAT,
	 TKNGANHANG	NVARCHAR(20),
	 MOTAINGANHANG	NVARCHAR(50),
	 DIACHI	NVARCHAR(255),
	 DIENTHOAI	NVARCHAR(10),
	 EMAIL	NVARCHAR(50),
	 CONSTRAINT PK_NHANVIEN	PRIMARY KEY(MANHANVIEN)
) 


-----------------BANG DON VI TINH-----------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='DONVITINH')
	DROP TABLE DONVITINH
GO
CREATE TABLE DONVITINH
(
	 MADONVITINH 	NVARCHAR(20) 	NOT NULL,
	 DIENGIAI	NVARCHAR(50)
	 CONSTRAINT PK_DONVITINH	PRIMARY KEY(MADONVITINH)
)
------------------BANG NHOM VAT TU--------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='NHOMVATTU')
	DROP TABLE NHOMVATTU
GO
CREATE TABLE NHOMVATTU
(
	 MANHOMVATTU 	NVARCHAR(20) 	NOT NULL,
	 TENNHOMVATTU	NVARCHAR(50),
	 CONSTRAINT PK_NVT	PRIMARY KEY(MANHOMVATTU)
)
------------------BANG VAT TU------------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='VATTU')
	DROP TABLE VATTU
GO
CREATE TABLE VATTU
(
	 MAVATTU 	NVARCHAR(20) 	NOT NULL,
	 TENVATTU 	NVARCHAR(50) 	NOT NULL,
	 TINHCHAT	NVARCHAR(50),
	 NHOM	NVARCHAR(20),
	 MOTA	NVARCHAR(255),
	 DONVITINH NVARCHAR(20),
	 THOIHANBH NVARCHAR(50),
	 SOLUONG INT,
	 NGUONGOC NVARCHAR(50),
	 CONSTRAINT PK_VATTU	PRIMARY KEY(MAVATTU)
)

------------------BANG PHIEU NHAP-----------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='PHIEUNHAP')
	DROP TABLE PHIEUNHAP
GO
CREATE TABLE PHIEUNHAP
( 
	MAPHIEUNHAP NVARCHAR(20) 	NOT NULL,
	MANCC		NVARCHAR(20) 	NOT NULL,
	 NGUOIGIAOHANG NVARCHAR(50),
	 DIENGIAI NVARCHAR(50),
	 NGAYHACHTOAN SMALLDATETIME,
	 NGAYCHUNGTU SMALLDATETIME,
	 SOCHUNGTU NVARCHAR(10),
	 CONSTRAINT PK_PHIEUNHAP	PRIMARY KEY(MAPHIEUNHAP)
)

------------------BANG CHI TIET NHAP-----------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='CHITIETNHAP')
	DROP TABLE CHITIETNHAP
GO
CREATE TABLE CHITIETNHAP
(
	 ID NVARCHAR(20) NOT NULL, 
	 MAPHIEUNHAP NVARCHAR(20) 	NOT NULL,
	 MAHANG NVARCHAR(20) NOT NULL,
	 MAKHO 	NVARCHAR(20) 	NOT NULL, 
	 SOLUONG INT, 
	 THANHTIEN NVARCHAR(20),
	 DONGIA NVARCHAR(20),
	 CONSTRAINT PK_CTN	PRIMARY KEY(ID)

)
------------------BANG TON KHO----------------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='TONKHO')
	DROP TABLE TONKHO
GO
CREATE TABLE TONKHO
(
	 MAVATTU 	NVARCHAR(20) 	NOT NULL,
	 SOLUONG INT,
	 GIATRITON NVARCHAR(20),
	 HANSUDUNG SMALLDATETIME,
	 CONSTRAINT PK_TONKHO	PRIMARY KEY(MAVATTU)
)

------------------BANG PHIEU XUAT-------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='PHIEUXUAT')
	DROP TABLE PHIEUXUAT
GO
CREATE TABLE PHIEUXUAT
(
	 MAPX 	NVARCHAR(20) 	NOT NULL,
	 MAKH	NVARCHAR(20) 	NOT NULL,
	 NGUOINHAN NVARCHAR(50),
	 DIACHI NVARCHAR(255),
	 LYDOXUAT NVARCHAR(255),
	 NHANVIEN NVARCHAR(255),
	 CONSTRAINT PK_PX	PRIMARY KEY(MAPX)
)

----------------BANG CHI TIET XUAT------------
GO
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME='CHITIETXUAT')
	DROP TABLE CHITIETXUAT
GO
CREATE TABLE CHITIETXUAT
( 
	 ID NVARCHAR(20) NOT NULL,
	 MAPHIEUXUAT NVARCHAR(20) 	NOT NULL,
	 MAHANG NVARCHAR(20) NOT NULL,
	 MAKHO 	NVARCHAR(20) 	NOT NULL, 
	 SOLUONG INT, 
	 THANHTIEN NVARCHAR(20),
	 DONGIA NVARCHAR(20),
	 CONSTRAINT PK_CTX	PRIMARY KEY(ID)

)
-----------------****************-------------
----------------TAO KHOA NGOAI--------------
GO
ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NV_DONVI FOREIGN KEY(DONVI) REFERENCES DONVI(MADONVI)
GO
ALTER TABLE KHACHHANG ADD CONSTRAINT FK_KH_MANHOM FOREIGN KEY(MANHOM) REFERENCES NHOM(MANHOM)
GO
ALTER TABLE NHACUNGCAP ADD CONSTRAINT FK_NCC_MANHOM FOREIGN KEY(MANHOM) REFERENCES NHOM(MANHOM)
GO
ALTER TABLE VATTU ADD CONSTRAINT FK_VT_DVT FOREIGN KEY(DONVITINH) REFERENCES DONVITINH(MADONVITINH)
GO
ALTER TABLE VATTU ADD CONSTRAINT FK_VT_NHOM FOREIGN KEY(NHOM) REFERENCES NHOMVATTU(MANHOMVATTU)
GO
ALTER TABLE TONKHO ADD CONSTRAINT FK_TK_MAVATTU FOREIGN KEY(MAVATTU) REFERENCES VATTU(MAVATTU)
GO
ALTER TABLE PHIEUNHAP ADD CONSTRAINT FK_NK_MANCC FOREIGN KEY(MANCC) REFERENCES NHACUNGCAP(MANCC)
GO
ALTER TABLE CHITIETNHAP ADD CONSTRAINT FK_CTN_MAKHO FOREIGN KEY(MAKHO) REFERENCES KHO(MAKHO)
GO
ALTER TABLE CHITIETNHAP ADD CONSTRAINT FK_CTN_MAHANG FOREIGN KEY(MAHANG) REFERENCES TONKHO(MAVATTU)
GO
ALTER TABLE CHITIETXUAT ADD CONSTRAINT FK_CTX_MAHANG FOREIGN KEY(MAHANG) REFERENCES TONKHO(MAVATTU)
GO
--ALTER TABLE CHITIETNHAP ADD CONSTRAINT FK_CTN_MAPN FOREIGN KEY(MAPHEUNHAP) REFERENCES PHIEUNHAP(MAPHEUNHAP)
--GO
--GO
--ALTER TABLE CHITIETXUAT ADD CONSTRAINT FK_CTX_MAPX FOREIGN KEY(MAPHEUXUAT) REFERENCES PHIEUXUAT(MAPX)

SELECT     CHITIETNHAP.ID, CHITIETNHAP.MAPHIEUNHAP, CHITIETNHAP.MAHANG, VATTU.TENVATTU, CHITIETNHAP.MAKHO, DONVITINH.DIENGIAI, 
                      CHITIETNHAP.SOLUONG, CHITIETNHAP.THANHTIEN, CHITIETNHAP.DONGIA, TONKHO.HANSUDUNG
FROM         CHITIETNHAP INNER JOIN
                      TONKHO ON CHITIETNHAP.MAHANG = TONKHO.MAVATTU INNER JOIN
                      VATTU INNER JOIN
                      DONVITINH ON VATTU.DONVITINH = DONVITINH.MADONVITINH ON TONKHO.MAVATTU = VATTU.MAVATTU
                      
                      
SELECT     CHITIETNHAP.ID, CHITIETNHAP.MAHANG, VATTU.TENVATTU, CHITIETNHAP.MAKHO, CHITIETNHAP.SOLUONG, CHITIETNHAP.THANHTIEN, 
                      CHITIETNHAP.DONGIA, DONVITINH.DIENGIAI
FROM         CHITIETNHAP INNER JOIN
                      KHO ON CHITIETNHAP.MAKHO = KHO.MAKHO INNER JOIN
                      TONKHO ON CHITIETNHAP.MAHANG = TONKHO.MAVATTU INNER JOIN
                      TONKHO AS TONKHO_1 ON CHITIETNHAP.MAHANG = TONKHO_1.MAVATTU INNER JOIN
                      VATTU ON TONKHO.MAVATTU = VATTU.MAVATTU AND TONKHO_1.MAVATTU = VATTU.MAVATTU INNER JOIN
                      DONVITINH ON VATTU.DONVITINH = DONVITINH.MADONVITINH


insert into NHOM values('NHOM1','HOA SEN',N'THIẾT BỊ GIA DỤNG',N'Khách Hảng')
insert into KHACHHANG values('KH01','BANH KEO','NHOM1','QUAN 9','0108989289','08.2425231')
insert into NHACUNGCAP values('NCC02','BANH KEO','QUAN 9','NHOM02','0108989289','08.2425231','','','')
insert into DONVI values('DV01','HANH CHINH','QUAN 9','PHONG BAN')
insert into NHANVIEN values('NV01','HAI HA',N'NU','2/2/1989','','','','','','DV01','','','','','','','')
